﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Reflection;

namespace QQ2564874169.RelationalSql
{
    public interface ISql : IDisposable
    {
        event EventHandler<SqlBeforeEventArgs> Before;
        event EventHandler<SqlAfterEventArgs> After;
        event EventHandler<SqlException> Error;
        event EventHandler<TransactionFinishEventArgs> TransactionBefore;
        event EventHandler<TransactionFinishEventArgs> TransactionAfter;

        IDictionary Data { get; }

        bool InTransaction { get; }

        int Execute(string sql, object param = null, bool isProc = false, int? timeout = null);

        T QueryScalar<T>(string sql, object param = null, bool isProc = false, int? timeout = null);

        IEnumerable<T> Query<T>(string sql, object param = null, bool isProc = false, int? timeout = null);

        IMultipleReader QueryMultiple(string sql, object param = null, bool isProc = false, int? timeout = null);

        ITransactionScope BeginTransaction();
    }

    public abstract class Sql : ISql
    {
        public IDictionary Data { get; private set; }
        public bool InTransaction => _tcount > 0;
        public static event Action<Sql> Created; 
        public event EventHandler<SqlBeforeEventArgs> Before;
        public event EventHandler<SqlAfterEventArgs> After;
        public event EventHandler<SqlException> Error;
        public event EventHandler<TransactionFinishEventArgs> TransactionBefore;
        public event EventHandler<TransactionFinishEventArgs> TransactionAfter;
        public string Id { get; }

        private int _tcount;

        protected Sql()
        {
            Data = new Hashtable();
            Created?.Invoke(this);
            Id = Guid.NewGuid().ToString();
        }

        public void Dispose()
        {
            Rollback();
            OnDispose();
            Data?.Clear();
            Data = null;
            Before = null;
            After = null;
            Error = null;
            TransactionBefore = null;
            TransactionAfter = null;
        }

        protected abstract void OnDispose();

        protected virtual SqlBeforeEventArgs OnBefore(SqlContext context)
        {
            var args = new SqlBeforeEventArgs
            {
                Context = context
            };
            Before?.Invoke(this, args);
            return args;
        }

        protected virtual void OnAfter(SqlBeforeEventArgs before)
        {
            var args = new SqlAfterEventArgs
            {
                Context = before.Context,
                Result = before.Result
            };
            After?.Invoke(this, args);
        }

        protected virtual void OnError(SqlContext context, Exception ex)
        {
            Error?.Invoke(this, new SqlException {Context = context, Error = ex});
        }

        protected virtual void OnTransactionBefore(bool isCommited)
        {
            TransactionBefore?.Invoke(this, new TransactionFinishEventArgs(isCommited));
        }
        protected virtual void OnTransactionAfter(bool isCommited)
        {
            TransactionAfter?.Invoke(this, new TransactionFinishEventArgs(isCommited));
        }

        protected abstract int OnExecute(SqlContext context);

        protected abstract T OnQueryScalar<T>(SqlContext context);

        protected abstract IEnumerable<T> OnQuery<T>(SqlContext context);

        protected abstract IMultipleReader OnQueryMultiple(SqlContext context);

        protected abstract void OnBeginTransaction();

        protected abstract void OnCommitTransaction();

        protected abstract void OnRollbackTransaction();

        protected virtual ITransactionScope OnCreateTransactionScope()
        {
            return new TransactionScope();
        }

        public int Execute(string sql, object param = null, bool isProc = false, int? timeout = null)
        {
            var context = new SqlContext
            {
                Command = sql,
                IsProc = isProc,
                Param = ConvertToParam(param),
                Timeout = timeout
            };
            try
            {
                var args = OnBefore(context);
                args.Result = args.Result ?? OnExecute(context);
                OnAfter(args);
                return (int) args.Result;
            }
            catch (Exception ex)
            {
                OnError(context, ex);
                throw;
            }
        }

        public T QueryScalar<T>(string sql, object param = null, bool isProc = false, int? timeout = null)
        {
            var context = new SqlContext
            {
                Command = sql,
                IsProc = isProc,
                Param = ConvertToParam(param),
                Timeout = timeout
            };
            try
            {
                var args = OnBefore(context);
                args.Result = args.Result ?? OnQueryScalar<T>(context);
                OnAfter(args);
                return (T) args.Result;
            }
            catch (Exception ex)
            {
                OnError(context, ex);
                throw;
            }
        }

        public IEnumerable<T> Query<T>(string sql, object param = null, bool isProc = false, int? timeout = null)
        {
            var context = new SqlContext
            {
                Command = sql,
                IsProc = isProc,
                Param = ConvertToParam(param),
                Timeout = timeout
            };
            try
            {
                var args = OnBefore(context);
                args.Result = args.Result ?? OnQuery<T>(context);
                OnAfter(args);
                return (IEnumerable<T>) args.Result;
            }
            catch (Exception ex)
            {
                OnError(context, ex);
                throw;
            }
        }

        public IMultipleReader QueryMultiple(string sql, object param = null, bool isProc = false, int? timeout = null)
        {
            var context = new SqlContext
            {
                Command = sql,
                IsProc = isProc,
                Param = ConvertToParam(param),
                Timeout = timeout
            };
            try
            {
                var args = OnBefore(context);
                args.Result = args.Result ?? OnQueryMultiple(context);
                var reader = (IMultipleReader) args.Result;
                reader.Disposed += (s, e) =>
                {
                    OnAfter(args);
                };
                return reader;
            }
            catch (Exception ex)
            {
                OnError(context, ex);
                throw;
            }
        }

        private void Rollback()
        {
            if (InTransaction == false)
                return;
            OnTransactionBefore(false);
            _tcount = 0;
            OnRollbackTransaction();
            OnTransactionAfter(false);
        }

        private void Commit()
        {
            if (InTransaction == false)
                return;
            OnTransactionBefore(true);
            _tcount = 0;
            OnCommitTransaction();
            OnTransactionAfter(true);
        }

        public ITransactionScope BeginTransaction()
        {
            if (_tcount++ < 1)
            {
                OnBeginTransaction();
            }
            var scope = OnCreateTransactionScope();

            scope.Finish += (s, e) =>
            {
                if (e.IsCommited)
                {
                    if (_tcount == 1)
                    {
                        Commit();
                    }
                    else
                    {
                        _tcount--;
                    }
                }
                else
                {
                    Rollback();
                }
            };
            return scope;
        }

        protected virtual object ConvertToParam(object param)
        {
            if (param == null) return null;

            var list = new List<SqlParamValue>();

            if (param is IDictionary)
            {
                var dict = (IDictionary)param;
                foreach (var item in dict)
                {
                    list.Add(ToParamValue(item));
                }
                return list.ToArray();
            }

            if (param is ICollection)
            {
                foreach (var item in (ICollection)param)
                {
                    list.Add(ToParamValue(item));
                }
                return list;
            }
            foreach (var p in param.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
            {
                var v = p.GetValue(param, null);
                if (v != null)
                {
                    var pv = ToParamValue(v);
                    pv.Name = p.Name;
                    list.Add(pv);
                }
            }
            return list.ToArray();
        }

        protected virtual SqlParamValue ToParamValue(object item)
        {
            if (item is SqlParamValue)
            {
                return (SqlParamValue) item;
            }
            if (item is DbParameter)
            {
                var p = (DbParameter) item;
                return new SqlParamValue
                {
                    Name = p.ParameterName,
                    Value = p.Value,
                    Type = p.DbType,
                    Size = p.Size
                };
            }
            if (item is DictionaryEntry)
            {
                var entry = (DictionaryEntry) item;
                return new SqlParamValue
                {
                    Name = entry.Key.ToString(),
                    Value = entry.Value
                };
            }
            return new SqlParamValue
            {
                Value = item
            };
        }
    }
}
